﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.SqlServer;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Web.UI.WebControls;
using System.IO;
using OpenSIS.OpenSISWCFServiceReference;
using System.Configuration;

namespace OpenSIS.Installation
{
    public class Installation
    {


        private string language;
        private string dateformat;
        private string timezone;
        private string databasetype;
        private string server;
        private string databasename;
        private string databaseusername;
        private string databasepassword;
        private string port;
        private string adminusername;
        private string adminpassword;
        private string schoolname;
        private string begindate;
        private string enddate;
        private string username;
        private string password;
        private string conn;
        private int schoolyrid;
        private int schoolid;

        private int mingrade;
        private int maxgrade;

        private string gradename;
        


        private string schoolyearname;


        public void InstallationBL()
        {
            mingrade = 0;
            maxgrade = 0;
            language = "";
            dateformat = "";
            timezone = "";
            databasetype = "";
            server = "";
            databasename = "";
            databaseusername = "";
            databasepassword = "";
            port = "";
            adminusername = "";
            adminpassword = "";
            schoolname = "";
            begindate = "01/01/1900";
            enddate = "01/01/1900";
            username = "";
            password = "";
            conn = "";
            schoolyrid = 0;
            schoolid = 0;
            Gradename = "";
            schoolyearname = "";

        }


        public string Gradename
        {
            get
            {
                return this.gradename;
            }
            set
            {
                this.gradename = value;
            }
        }

        public String SchoolYearName
        {
            get
            {
                return this.schoolyearname;
            }
            set
            {
                this.schoolyearname = value;
            }
        }



        public int Mingrade
        {
            get
            {
                return this.mingrade;
            }
            set
            {
                this.mingrade = value;
            }
        }
        public int Maxgrade
        {
            get
            {
                return this.maxgrade;
            }
            set
            {
                this.maxgrade = value;
            }
        }


        public int Schoolid
        {
            get
            {
                return this.schoolid;
            }
            set
            {
                this.schoolid = value;
            }
        }




        public int Schoolyrid
        {
            get
            {
                return this.schoolyrid;
            }
            set
            {
                this.schoolyrid = value;
            }
        }

        public String Conn
        {
            get
            {
                return this.conn;
            }
            set
            {
                this.conn = value;
            }
        }
        public String Username
        {
            get
            {
                return this.username;
            }
            set
            {
                this.username = value;
            }
        }
        public String Password
        {
            get
            {
                return this.password;
            }
            set
            {
                this.password = value;
            }
        }
        public string Language
        {
            get
            {
                return this.language;
            }
            set
            {
                this.language = value;
            }
        }
        public string Dateformat
        {
            get
            {
                return this.dateformat;
            }
            set
            {
                this.dateformat = value;
            }
        }

        public string TimeZone
        {
            get
            {
                return this.timezone;
            }
            set
            {
                this.timezone = value;
            }
        }

        public string DatabaseType
        {
            get
            {
                return this.databasetype;
            }
            set
            {
                this.databasetype = value;
            }
        }
        public String Server
        {
            get
            {
                return this.server;
            }
            set
            {
                this.server = value;
            }
        }
        public String Databasename
        {
            get
            {
                return this.databasename;
            }
            set
            {
                this.databasename = value;
            }
        }
        public String DatabaseUsername
        {
            get
            {
                return this.databaseusername;
            }
            set
            {
                this.databaseusername = value;
            }
        }
        public String DatabasePassword
        {
            get
            {
                return this.databasepassword;
            }
            set
            {
                this.databasepassword = value;
            }
        }

        public String Port
        {
            get
            {
                return this.port;
            }
            set
            {
                this.port = value;
            }
        }

        public String AdminUsername
        {
            get
            {
                return this.adminusername;
            }
            set
            {
                this.adminusername = value;
            }
        }
        public String AdminPassword
        {
            get
            {
                return this.adminpassword;
            }
            set
            {
                this.adminpassword = value;
            }
        }
        public String SchoolName
        {
            get
            {
                return this.schoolname;
            }
            set
            {
                this.schoolname = value;
            }
        }

        public String BeginDate
        {
            get
            {
                return this.begindate;
            }
            set
            {
                this.begindate = value;
            }
        }
        public String EndDate
        {
            get
            {
                return this.enddate;
            }
            set
            {
                this.enddate = value;
            }
        }













       public static string cnnstring;
       public  static string serverinst;

        string SQL_CONNECTION_STRING;
        string connectionString;
        string DBServer;

        private string DBName = "";
        private string DBCreateDataFile = "";

        protected bool bolDidPreviouslyConnect = false;

        private string fileUrl = "";
        private int timeout = 600;

      






        public void PopulateDateFormat(ref System.Web.UI.WebControls.DropDownList ddl)
        {
           
            ddl.Items.Clear();
           
            ddl.Items.Insert(0, new ListItem("Select", "select"));
            ddl.Items.Insert(1, new ListItem("MM/dd/yyyy", "MM/dd/yyyy"));        
            ddl.Items.Insert(2, new ListItem("MM-dd-yyyy", "MM-dd-yyyy"));
            ddl.Items.Insert(3, new ListItem("MM-dd-yy", "MM-dd-yy"));           
            ddl.Items.Insert(4, new ListItem("MM/dd/yy", "MM/dd/yy"));

        }


        public void PopulateLanguages(ref System.Web.UI.WebControls.DropDownList ddl)
        {
           // InstallationDL INDL = new InstallationDL();
            ddl.Items.Clear();
            ddl.Items.Insert(0, new ListItem("Select", "select"));
            ddl.Items.Insert(1, new ListItem("English", "en"));
            ddl.Items.Insert(2, new ListItem("Spanish", "es"));
            ddl.Items.Insert(3, new ListItem("Chinese", "zh-CHS"));
            ddl.Items.Insert(4, new ListItem("Japanese", "ja"));
            ddl.Items.Insert(5, new ListItem("German", "de"));
            ddl.Items.Insert(6, new ListItem("Korean", "ko"));
            ddl.Items.Insert(7, new ListItem("French", "fr"));
            ddl.Items.Insert(8, new ListItem("Italian", "it"));
            ddl.Items.Insert(9, new ListItem("Russian", "ru"));
            ddl.Items.Insert(10, new ListItem("Portuguese", "pt"));
            ddl.Items.Insert(11, new ListItem("Arabic", "ar"));


        }

        public void PopulateDatabaseType(ref System.Web.UI.WebControls.RadioButtonList rbl)
        {
         
            rbl.Items.Clear();

           
            rbl.Items.Insert(0, new ListItem("Microsoft SQL Server", "msql"));
            rbl.Items.Insert(1, new ListItem("MySQL", "mysql"));
            rbl.Items.Insert(2, new ListItem("Oracle", "Oracle"));
            rbl.Items.Insert(3, new ListItem("DB2", "db2"));
        }




        private bool CreateInstanceDb(string strUname, string strPassword, string server)
        {

          
            DatabaseType = server;

            if (DatabaseType == "msql")
            {
                string strSQL = "IF EXISTS (SELECT [name] FROM Master..sysdatabases WHERE [name] = N'" + DBName + "')" + Environment.NewLine + "BEGIN" + Environment.NewLine + "EXEC dbo.clearDBUsers " + DBName + "" + Environment.NewLine + "DROP DATABASE " + DBName + "" + Environment.NewLine + "END" + Environment.NewLine + "CREATE DATABASE " + DBName;


                bool bolIsConnecting = true;
                while (bolIsConnecting)
                {
                    try
                    {

                        SqlConnection northwindConnection = new SqlConnection(SQL_CONNECTION_STRING);


                        SqlCommand cmd = new SqlCommand(strSQL, northwindConnection);


                        northwindConnection.Open();
                        cmd.ExecuteNonQuery();
                        northwindConnection.Close();

                        bolIsConnecting = false;
                        bolDidPreviouslyConnect = true;

                        return true;
                    }
                    catch (SqlException sqlExc)
                    {
                        return false;

                    }
                    catch (Exception exc)
                    {
                        if (connectionString == SQL_CONNECTION_STRING)
                        {
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
                return true;


            }
            //Mysql Implementaion
            else
            {
                string strSQL = "CREATE DATABASE IF NOT EXISTS `" + DBName + "`";


                bool bolIsConnecting = true;
                while (bolIsConnecting)
                {
                    try
                    {

                        MySqlConnection Connection = new MySqlConnection(SQL_CONNECTION_STRING);


                        MySqlCommand cmd = new MySqlCommand(strSQL, Connection);


                        Connection.Open();
                        cmd.ExecuteNonQuery();
                        Connection.Close();

                        bolIsConnecting = false;
                        bolDidPreviouslyConnect = true;

                        return true;
                    }
                    catch (MySqlException sqlExc)
                    {
                        return false;

                    }
                    catch (Exception exc)
                    {
                        if (connectionString == SQL_CONNECTION_STRING)
                        {
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
                return true;

            }


        }

        public bool CreateSQLDb(string strServerName, string strDBName, String strUserName, string strPass, string server)
        {
           // InstallationBL INBL = new InstallationBL();

            string servertype = server;

            if (servertype == "msql")
            {
                DBName = strDBName;
                DBServer = strServerName;
                if (strUserName.Trim().Length > 0)
                {
                    SQL_CONNECTION_STRING = "User ID=" + strUserName + ";Pwd=" + strPass + ";Data Source=" + strServerName + ";database=;Persist Security Info=False;";
                }
                else
                {
                    SQL_CONNECTION_STRING = "Server=" + DBServer + ";" + "DataBase=;" + "Integrated Security=SSPI;";
                }
                connectionString = SQL_CONNECTION_STRING;
                if (CreateInstanceDb(strUserName, strPass, server))
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            //MYSQL Implementation
            else
            {
                DBName = strDBName;
                DBServer = strServerName;
                if (strUserName.Trim().Length > 0)
                {

                    SQL_CONNECTION_STRING = "uid=" + strUserName + ";pwd=" + strPass + ";Data Source=" + strServerName + " ;Character Set=utf8;";
                }
                else
                {

                }
                connectionString = SQL_CONNECTION_STRING;
                if (CreateInstanceDb(strUserName, strPass, server))
                {
                    return true;
                }
                else
                {
                    return false;
                }



            }







        }

        public string ConnString
        {

            get { return connectionString; }
            set
            {
                connectionString = value;
                SQL_CONNECTION_STRING = value;
            }
        }


        public bool ExecuteSQLScript(string SQlFilePath, string ConStr, string server)
        {

          
            DatabaseType = server;
            try
            {

                fileUrl = SQlFilePath;
                System.Net.WebRequest request = System.Net.WebRequest.Create(fileUrl);
                using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream()))
                {
                    if (DatabaseType == "msql")
                    {
                        SqlConnection conn = null;
                        conn = new SqlConnection(ConStr);

                        conn.Open();

                        while (!sr.EndOfStream)
                        {
                            System.Text.StringBuilder sb = new System.Text.StringBuilder();
                            SqlCommand cmd = conn.CreateCommand();

                            while (!sr.EndOfStream)
                            {
                                string s = sr.ReadLine();
                                if (s != null && s.ToUpper().Trim().Equals("GO"))
                                {
                                    break;
                                }

                                sb.AppendLine(s);
                            }


                            cmd.CommandText = sb.ToString();
                            cmd.CommandTimeout = timeout;


                            cmd.ExecuteNonQuery();


                        }



                        string path = HttpContext.Current.Server.MapPath("~/Web.config");


                        System.Xml.XmlDocument xDoc = new System.Xml.XmlDocument();

                        xDoc.Load(path);

                        System.Xml.XmlNodeList nodeList = xDoc.GetElementsByTagName("appSettings");

                        System.Xml.XmlNodeList nodeAppSettings = nodeList[0].ChildNodes;

                        System.Xml.XmlAttributeCollection xmlAttCollection = nodeAppSettings[0].Attributes;

                        xmlAttCollection[0].InnerXml = "sqlConn"; // for key attribute

                        xmlAttCollection[1].InnerXml = "Persist Security Info=False;Provider=SQLOLEDB;" + ConStr; // for value attribute



                        System.Xml.XmlNodeList nodeAppSettingsserv = nodeList[0].ChildNodes;

                        System.Xml.XmlAttributeCollection xmlAttCollectionserv = nodeAppSettingsserv[1].Attributes;


                        xmlAttCollectionserv[0].InnerXml = "appserver"; // for key attribute

                        xmlAttCollectionserv[1].InnerXml = "MSSQLServer";


                        System.Xml.XmlNodeList nodeAppSettingscal = nodeList[0].ChildNodes;

                        System.Xml.XmlAttributeCollection xmlAttCollectioncal = nodeAppSettingscal[2].Attributes;


                        xmlAttCollectioncal[0].InnerXml = "calendarServer"; // for key attribute

                        xmlAttCollectioncal[1].InnerXml = "MSSQLServer";


                        xDoc.Save(path);

                        ////static connection string
                        cnnstring = "Persist Security Info=False;Provider=SQLOLEDB;" + ConStr;

                        serverinst = "MSSQLServer";

                    }

                        //MYSQL Implementation
                    else
                    {
                        MySqlConnection conn = null;
                        conn = new MySqlConnection(ConStr);

                        conn.Open();

                        while (!sr.EndOfStream)
                        {
                            System.Text.StringBuilder sb = new System.Text.StringBuilder();
                            MySqlCommand cmd = conn.CreateCommand();

                            while (!sr.EndOfStream)
                            {
                                string s = sr.ReadLine();
                                if (s != null && s.ToUpper().Trim().Equals("GO"))
                                {
                                    break;
                                }

                                sb.AppendLine(s);
                                if (s.Contains("INNODB"))
                                {
                                    cmd.CommandText = sb.ToString();
                                    cmd.CommandTimeout = timeout;


                                    cmd.ExecuteNonQuery();

                                    sb.Remove(0, sb.Length);

                                }
                            }







                        }



                        string path = HttpContext.Current.Server.MapPath("~/Web.config");


                        System.Xml.XmlDocument xDoc = new System.Xml.XmlDocument();

                        xDoc.Load(path);

                        System.Xml.XmlNodeList nodeList = xDoc.GetElementsByTagName("appSettings");

                        System.Xml.XmlNodeList nodeAppSettings = nodeList[0].ChildNodes;


                        System.Xml.XmlAttributeCollection xmlAttCollection = nodeAppSettings[0].Attributes;



                        xmlAttCollection[0].InnerXml = "sqlConn"; // for key attribute

                        xmlAttCollection[1].InnerXml = ConStr; // for value attribute


                        System.Xml.XmlNodeList nodeAppSettingsserv = nodeList[0].ChildNodes;

                        System.Xml.XmlAttributeCollection xmlAttCollectionserv = nodeAppSettingsserv[1].Attributes;


                        xmlAttCollectionserv[0].InnerXml = "appserver"; // for key attribute

                        xmlAttCollectionserv[1].InnerXml = "MySQL";



                        System.Xml.XmlNodeList nodeAppSettingscal = nodeList[0].ChildNodes;

                        System.Xml.XmlAttributeCollection xmlAttCollectioncal = nodeAppSettingscal[2].Attributes;


                        xmlAttCollectioncal[0].InnerXml = "calendarServer"; // for key attribute

                        xmlAttCollectioncal[1].InnerXml = "MySQL";


                        xDoc.Save(path);

                        ////static connection string
                        cnnstring = ConStr;

                        serverinst = "MySQL";
                       

                    }



                }
                //ConfigurationManager.RefreshSection("appSettings");
               

                OpenSISWCFServiceInstallationClient INstallClient = new OpenSISWCFServiceInstallationClient();

                INstallClient.InsertConnectionSettings(cnnstring, serverinst);
               // INstallClient.Close();
                return true;
            }
            catch (Exception ex)
            {

                return false;
            }
            finally
            {
            }
        }

    }
}